package bancodedados;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Database {
	public static boolean IS_SERVER_DEBUG_MODE = true;
	private static Database database;

	// server run
	private static final String URL = "jdbc:postgresql://krahn.no-ip.org:5432/casa";
	private static final String USER = "postgres";
	private static final String PASSWORD = "eng.software";
	
	public static Database getInstance() {
		if (database == null) {
			database = new Database();
		}
		return database;
	}

	public Connection startConnetion() throws SQLException {
		Connection con = null;

		try {
			//THIS IS REQUIRED! It registers the postgresql driver to the JDBC.
			new org.postgresql.Driver();
			
			con = (Connection) DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (SQLException e) {
			// Throwing a custom message to avoid security problems
			throw new SQLException("Could not connect to database! " + (IS_SERVER_DEBUG_MODE ? e.getMessage() : ""));
		}

		return con;
	}

	public int insert(String table, String fields, String values) throws SQLException {
		Connection con = null;
		PreparedStatement stmt = null;

		int insertedRows = 0;
		try {
			con = this.startConnetion();
			stmt = con.prepareStatement("insert into " + table + "(" + fields + ")" + " values " + "(" + values + ")");
			
			insertedRows = stmt.executeUpdate();
		} finally {
			if (stmt != null){
				stmt.close();
			}
			if (con != null){
				con.close();
			}
		}
		
		return insertedRows;
	}
	
	/**
	 * Used in complex insert statements
	 * 
	 * @author Daniel Krahn
	 * @param bigInsertStatement
	 * @return The number of inserted rows
	 * @throws SQLException
	 */
	public int insert(String bigInsertStatement) throws SQLException {
		Connection con = null;
		PreparedStatement stmt = null;

		int insertedRows = 0;
		try {
			con = this.startConnetion();
			stmt = con.prepareStatement(bigInsertStatement);
			
			insertedRows = stmt.executeUpdate();
		} finally {
			if (stmt != null){
				stmt.close();
			}
			if (con != null){
				con.close();
			}
		}
		return insertedRows;
	}

	public int insert(String table, String fields, String selectFilterColumns, String selectFilterTable, String selectFilterWhere) throws SQLException {
		Connection con = null;
		PreparedStatement stmt = null;
		
		int insertedRows = 0;
		try {
			con = this.startConnetion();
			stmt = con.prepareStatement("insert into " + table + "(" + fields + ")" + 
										"( select " + selectFilterColumns + " from " + selectFilterTable +
										" where " + selectFilterWhere + ")"
									);

			insertedRows = stmt.executeUpdate();
		} finally {
			if (stmt != null){
				stmt.close();
			}
			if (con != null){
				con.close();
			}
		}
		return insertedRows;
	}

	public int update(String table, String set, String where) throws SQLException {
		Connection con = null;
		PreparedStatement stmt = null;

		int updatedRows = 0;
		try {
			con = this.startConnetion();
			stmt = con.prepareStatement("update " + table + " set " + set + " where " + where);
			
			updatedRows = stmt.executeUpdate();
		} finally {
			if (stmt != null){
				stmt.close();
			}
			if (con != null){
				con.close();
			}
		}
		
		return updatedRows;
	}

	public int updateFrom(String table, String set, String from, String where) throws SQLException {
		Connection con = null;
		PreparedStatement stmt = null;
		int updatedRows = 0;
		
		try {
			con = this.startConnetion();
			stmt = con.prepareStatement("update " + table + " set " + set + " from " + from + " where " + where);
			
			updatedRows = stmt.executeUpdate();
		} finally {
			if (stmt != null){
				stmt.close();
			}
			if (con != null){
				con.close();
			}
		}
		
		return updatedRows;
	}
	
	public int delete(String table, String where) throws SQLException {
		Connection con = null;
		PreparedStatement stmt = null;
		int deletedRows;
		
		try {
			con = this.startConnetion();
			stmt = con.prepareStatement("delete from " + table + " where " + where);

			deletedRows = stmt.executeUpdate();
		} finally {
			if (stmt != null){
				stmt.close();
			}
			if (con != null){
				con.close();
			}
		}
		return deletedRows;
	}
	
	/**
	 * To do a select, we provide an instance of SimpleResultSetDataFetcher, which has a method
	 * that should be implemented in order to retrieve the data from the select. It is 
	 * done this way because it encapsulates the database connection and resultSet 
	 * connection, which has to be closed after the data is retrieved.
	 * 
	 * @param dataFetcherInstance an instance of SimpleResultSetDataFetcher<TYPE>
	 * @throws SQLException
	 * @throws Exception 
	 */
	public void doSelect(ResultSetDataFetcher<?> dataFetcherInstance) throws SQLException{
		String selectionString = dataFetcherInstance.getSelectionString();
		
		Connection connection = null;
		Statement stmt = null;
		try{
			connection = this.startConnetion();
			stmt = connection.createStatement();
			ResultSet resultSet = stmt.executeQuery(selectionString);
			try{
				dataFetcherInstance.fetchDataFromResultSet(resultSet);
			}finally{
				resultSet.close();
			}
		} finally{
			if (stmt != null){
				stmt.close();
			}
			if (connection != null){
				connection.close();
			}
		}		
	}
}